﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using DTO;

/// <summary>
/// Summary description for DuLieuDAO
/// </summary>
/// 
namespace DAO
{
    public class DuLieuDAO:dataProvider
    {

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            DuLieuDTO dlDTO = new DuLieuDTO();
            dlDTO.MaDuLieu = long.Parse(dt.Rows[i]["MaDuLieu"].ToString());
            dlDTO.MaDichVu = int.Parse(dt.Rows[i]["MaDichVu"].ToString());
            dlDTO.MaTenDiaDiem = long.Parse(dt.Rows[i]["MaTenDiaDiem"].ToString());
           
            dlDTO.SoNha = dt.Rows[i]["SoNha"].ToString();
            dlDTO.MaDuong = long.Parse(dt.Rows[i]["MaDuong"].ToString());
            dlDTO.MaPhuong = int.Parse(dt.Rows[i]["MaPhuong"].ToString());
            dlDTO.MaQuanHuyen = int.Parse(dt.Rows[i]["MaQuanHuyen"].ToString());
            dlDTO.MaTinhThanh = int.Parse(dt.Rows[i]["MaTinhThanh"].ToString());
          
            dlDTO.KinhDo =  double.Parse(dt.Rows[i]["KinhDo"].ToString());
            dlDTO.ViDo = double.Parse(dt.Rows[i]["ViDo"].ToString());

            dlDTO.ChuThich = dt.Rows[i]["ChuThich"].ToString();

            return (object)dlDTO;
        }

        public bool insert(DuLieuDTO dlDTO)
        {
            long MaDuLieu = long.Parse(this.nextID(this.getLastID(), ""));
            string strSQL = "Insert Into DULIEU(MaDuLieu,MaDichVu,MaTenDiaDiem,SoNha,MaDuong,MaPhuong,MaQuanHuyen,MaTinhThanh,KinhDo,ViDo,ChuThich)"
                + " values(" + MaDuLieu + "," + dlDTO.MaDichVu + "," + dlDTO.MaTenDiaDiem + ",'" + dlDTO.SoNha + "'," + dlDTO.MaDuong + "," + dlDTO.MaPhuong + "," + dlDTO.MaQuanHuyen + "," + dlDTO.MaTinhThanh + "," + dlDTO.KinhDo + "," + dlDTO.ViDo + ",'" + dlDTO.ChuThich + "')";
            return new dataProvider().executeNonQuery(strSQL);
        }
        public string getLastID()
        {
            return this.getLastID("DULIEU", "MaDuLieu");
        }
        // focus
        public bool insert2(DuLieuDTO dlDTO)
        {
            string strSQL = "Insert Into DULIEU(MaDichVu,MaTenDiaDiem,SoNha,MaDuong,MaPhuong,MaQuanHuyen,MaTinhThanh,KinhDo,ViDo,ChuThich)"
            + " values(" + dlDTO.MaDichVu + "," + dlDTO.MaTenDiaDiem + ",'" + dlDTO.SoNha + "'," + dlDTO.MaDuong + "," + dlDTO.MaPhuong + "," + dlDTO.MaQuanHuyen + "," + dlDTO.MaTinhThanh + "," + dlDTO.KinhDo + "," + dlDTO.ViDo + ",'" + dlDTO.ChuThich + "')";
            return new dataProvider().executeNonQuery(strSQL);
        }
        public bool delete(long MaDuLieu)
        {
            string strSQL = "Delete * from DULIEU Where DULIEU.MaDuLieu = " + MaDuLieu;
            return this.executeNonQuery(strSQL);
        }
        public bool update(DuLieuDTO dlDTO)
        {
            string strSQL = "Update DULIEU set MaDichVu = " + dlDTO.MaDichVu +",MaTenDiaDiem = " + dlDTO.MaTenDiaDiem + ",SoNha='" + dlDTO.SoNha +"',MaDuong = " + dlDTO.MaDuong + ",MaPhuong = " + dlDTO.MaPhuong + ",MaQuanHuyen=" + dlDTO.MaQuanHuyen + ",MaTinhThanh=" + dlDTO.MaTinhThanh + ",KinhDo = " + dlDTO.KinhDo + ",ViDo = " + dlDTO.ViDo + ",ChuThich= '" + dlDTO.ChuThich + "' Where DULIEU.MaDuLieu = " + dlDTO.MaDuLieu;
            return new dataProvider().executeNonQuery(strSQL);
        }
        public DuLieuDTO[] getDuLieuByDichVu(int MaDichVu)
        {
            DataTable dt = new DataTable();
            string strSQL = "Select * from DULIEU Where MaDichVu =" + MaDichVu;
            dt =this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }
        public DuLieuDTO[] getDuLieuByDiaDiem(int MaTenDiaDiem,int MaDichVu)
        {
            string strSQL = "Select * from DULIEU Where MaTenDiaDiem =" + MaTenDiaDiem + " and MaDichVu=" + MaDichVu;
            DataTable dt = new DataTable();
            dt = this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }

        public DuLieuDTO[] getDuLieuByQuan(int MaQuan,int MaDichVu)
        {
            string strSQL = "Select * from DULIEU Where DULIEU.MaQuanHuyen =" + MaQuan + " and MaDichVu=" + MaDichVu;
            DataTable dt = new DataTable();
            dt = this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }
        public DuLieuDTO[] getDuLieuById(long MaDuLieu)
        {
            string strSQL = "Select * from DULIEU Where DULIEU.MaDuLieu =" + MaDuLieu;
            DataTable dt = new DataTable();
            dt = this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }
        public DuLieuDTO[] getDuLieuByTinhThanh(int MaTinhThanh,int MaDichVu)
        {
            string strSQL = "Select * from DULIEU Where  MaTinhThanh =" + MaTinhThanh + " and MaDichVu=" + 1;
            DataTable dt = new DataTable();
            dt = this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }
        public DuLieuDTO[] getDuLieuByDuong(int MaDuong,int MaDichVu)
        {
            string strSQL = "Select * from DULIEU Where  MaDuong =" + MaDuong + " and MaDichVu=" + 1 ;
            DataTable dt = new DataTable();
            dt =this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }
        public DuLieuDTO[] getDuLieuByPhuong(int MaPhuong,int MaDichVu)
        {
            string strSQL = "Select * from DULIEU Where  MaPhuong =" + MaPhuong + " and MaDichVu=" + MaDichVu;
            DataTable dt = new DataTable();
            dt = this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }
        public DuLieuDTO[] getDulieuBy_DichVu_Duong_Quan(int MaDichVu, int MaTenDiaDiem, int MaDuong, int MaQuan)
        {
            //string strSQL = "Select * From DULIEU Where MaDichVu = " + MaDichVu + " and MaDuong = " + MaDuong + " and MaQuanHuyen = " + MaQuan ;
            //string strSQL = "Select * From DULIEU Where MaDichVu like " + MaDichVu + " and MaDuong like " + MaDuong + " and MaQuanHuyen like " + MaQuan;

            string strSQL = "SELECT * " +
                            "FROM DULIEU " +
                            "WHERE DULIEU.MaDichVu = " + MaDichVu;
            
            // Nếu có nhập vào tên địa điểm
            if (MaTenDiaDiem != -1)
            {
                strSQL += " and DULIEU.MaTenDiaDiem = " + MaTenDiaDiem ;
            }
            // Nếu có nhập tên đường vào
            if (MaDuong != -1)
            {
                strSQL += " and DULIEU.MaDuong = " + MaDuong;
            }

            // Nếu có nhập tên quận vào
            if (MaQuan != -1)
            {
                strSQL += " and DULIEU.MaQuanHuyen = " + MaQuan;
            }
            DataTable dt = new DataTable();
            dt = this.executeQuery(strSQL);
            DuLieuDTO[] obj = new DuLieuDTO[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj[i] = (DuLieuDTO)GetDataFromDataRow(dt, i);
            }
            return obj;
        }
    }
}

